﻿drop table "INTEX"."TechMaster"
go
  CREATE TABLE "INTEX"."TechMaster"
   (    "FClothNO" varchar(20),
        "PRDGRP" varchar(3),
        "ARTGRP" varchar(3),
        "FAssortment" varchar(20),
        "FColorNO" varchar(20),
        "FDesignName" varchar(20),
        "FDesignDate" varchar(20),
        "FLengthUnit" varchar(20),
        "FDeliveLength" varchar(20),
        "FGrayLength" varchar(20),
        "FBeamingLength" varchar(20),
        "FWeavingMachineCode" varchar(20),
        "FGrayBreadth_CM" varchar(20),
        "FGrayWarpDensity_Inch" varchar(20),
        "FGrayWeftDensity_Inch" varchar(20),
        "FFinishedBreadth_CM" varchar(20),
        "FGrayWeftDensityOnLoom" varchar(20),
        "FFinishedWarpDensity_Inch" varchar(20),
        "FFinishedWeftDensity_Inch" varchar(20),
        "FWeavingName" varchar(20),
        "FDrawingBody" varchar(20),
        "FWeftThreadCount" varchar(20),
        "FWarpThreadCount" varchar(20),
        "FFinishingDemand" varchar(20),
        "FTotalEnds" varchar(20),
        "FWarpEnds" varchar(20),
        "FWeftEnds" varchar(20),
        "FWarpUsage" varchar(20),
		"FFinishCode" varchar(10),
        "FWeftUsage" varchar(20)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"

GO


select M.布号 as FClothNO,
'JLT' as PRDGRP,'JLT' as ARTGRP,
品名 As FAssortment,
M.布号 as FColorNO,
设计员 as FDesignName,
日期 as FDesignDate,
'M  ' as FLengthUnit,
100 as FDeliveLength,
100 as FGrayLength,
100 as FBeamingLength,
织机 as FWeavingMachineCode,
胚布幅宽 as FGrayBreadth_CM,
胚布经密 as FGrayWarpDensity_Inch,
胚布纬密 as FGrayWeftDensity_Inch,
成品幅宽 as FFinishedBreadth_CM,
机上纬密 as FGrayWeftDensity_Inch, 
成品经密 as FFinishedWarpDensity_Inch,
成品纬密 as FFinishedWeftDensity_Inch,

组织 as FWeavingName,
布身穿法 as FDrawingBody,
len(纬纱支数)-len(replace(纬纱支数,'，','')) as FWeftThreadCount,
len(经纱支数)-len(replace(经纱支数,'，','')) as FWarpThreadCount,
订量 as FGrayLength,
订量 as FDeliveLength,
整经长 as FBeamingLength,
加工方法 as FFinishingDemand,
substr(加工方法,1,10) as FFinishCode,
总根数 as FTotalEnds,
 色纱排列 as WSeq,布边排列 as SSeq,经纱颜色 as WColor,纬纱颜色 as FColor,经纱自定KG as WCUsage,经纱代号 as WYarn,
 经纱支数 as WYCount,经纱色号 as WYColor,,经纱色名 as WYCDes,经纱损耗 as WUsage,纬纱代号 as FYarn,纬纱支数 as FYCount,
纬纱色号 as FYColor,纬纱色名 as FYCDes,纬纱损耗 as YUsage,纬纱梭序 as YSeq,纬纱自定KG as YCUsage,
 CAST(经纱条数1 as varchar)+','+CAST(经纱条数2 as varchar)
+','+CAST(经纱条数3 as varchar)
+','+CAST(经纱条数4 as varchar)
+','+CAST(经纱条数5 as varchar)
+','+CAST(经纱条数6 as varchar)
+','+CAST(经纱条数7 as varchar)
+','+CAST(经纱条数8 as varchar)
+','+CAST(经纱条数9 as varchar)
+','+CAST(经纱条数10 as varchar)
+','+CAST(经纱条数11 as varchar)
+','+CAST(经纱条数12 as varchar)
+','+CAST(经纱条数13 as varchar)
+','+CAST(经纱条数14 as varchar)
+','+CAST(经纱条数15 as varchar) as 经纱条数,
 CAST(纬纱条数1 as varchar)+','+CAST(纬纱条数2 as varchar)
+','+CAST(纬纱条数3 as varchar)
+','+CAST(纬纱条数4 as varchar)
+','+CAST(纬纱条数5 as varchar)
+','+CAST(纬纱条数6 as varchar)
+','+CAST(纬纱条数7 as varchar)
+','+CAST(纬纱条数8 as varchar) as 纬纱条数,
 CAST(经纱用量1 as varchar)+','+CAST(经纱用量2 as varchar)
+','+CAST(经纱用量3 as varchar)
+','+CAST(经纱用量4 as varchar)
+','+CAST(经纱用量5 as varchar)
+','+CAST(经纱用量6 as varchar)
+','+CAST(经纱用量7 as varchar)
+','+CAST(经纱用量8 as varchar)
+','+CAST(经纱用量9 as varchar)
+','+CAST(经纱用量10 as varchar)
+','+CAST(经纱用量11 as varchar)
+','+CAST(经纱用量12 as varchar)
+','+CAST(经纱用量13 as varchar)
+','+CAST(经纱用量14 as varchar)
+','+CAST(经纱用量15 as varchar) as 经纱用量,
 CAST(纬纱用量1 as varchar)+','+CAST(纬纱用量2 as varchar)
+','+CAST(纬纱用量3 as varchar)
+','+CAST(纬纱用量4 as varchar)
+','+CAST(纬纱用量5 as varchar)
+','+CAST(纬纱用量6 as varchar)
+','+CAST(纬纱用量7 as varchar)
+','+CAST(纬纱用量8 as varchar) as 纬纱用量
from 规格表 M LEFT JOIN 经纬纱 Y ON M.布号=Y.布号
where M.布号 ='JLZ13-0001'  